import json
import os
import sqlite3


def CreateDatabase():
    conn = sqlite3.connect("./poem.db")
    cursor = conn.cursor()

    # 解析JSON数据
    with open('resources/dynasty.json', 'r') as file:
        dynasty_data = json.load(file)

        # 创建朝代表
        cursor.execute(
            '''CREATE TABLE IF NOT EXISTS dynasty (name TEXT,  start_year integer,  end_year integer,  description TEXT)''')

        # 批量插入数据
        for dynasty in dynasty_data:
            cursor.executescript(
                f"""INSERT INTO dynasty (name, start_year, end_year, description) VALUES ('{dynasty['name']}', '{dynasty['start_year']}', '{dynasty['end_year']}', '{dynasty['description']}')"""
            )

    # 创建诗人表
    cursor.execute(
        '''CREATE TABLE IF NOT EXISTS poet (name TEXT,  dynasty TEXT,  start_year integer,  end_year integer, description TEXT)''')
    poet_files = os.listdir("resources/poets")
    for poet_file in poet_files:
        with open(f"resources/poets/{poet_file}", 'r') as file:
            poet_data = json.load(file)
            # 批量插入数据
            for poet in poet_data:
                cursor.executescript(
                    f"""INSERT INTO poet (name, dynasty, start_year, end_year, description) VALUES ('{poet['name']}', '{poet['dynasty']}', '{poet['start_year']}', '{poet['end_year']}', '{poet['description']}')"""
                )

    # 提交和关闭
    conn.commit()
    cursor.close()
    conn.close()


if __name__ == "__main__":
    CreateDatabase()
